﻿using Dapper;
using Dapper.Contrib.Extensions;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Tauren.Form.Entity.Global;
using Tauren.Form.Entity.Model;
using Tauren.Form.Entity.Output;
using Tauren.Form.Infrastructure.Globals;

namespace Tauren.Form.DLL.Form_TableInfoDLL
{
    /// <summary>
    /// 设置表单需要的表信息
    /// </summary>
    internal class Form_TableInfoBusiness : Base.BaseDLL, IForm_TableInfoBusiness
    {
        public Form_TableInfoBusiness(IConnectionDLLBase _dLLBase) : base(_dLLBase)
        {
        }

        /// <summary>
        /// 获取主表中的子表信息
        /// </summary>
        /// <param name="ParentId"></param>
        /// <returns></returns>
        public List<Form_Child_TableInfo_OutPut> GetForm_Child_TableInfo_OutPuts(long ParentId) => BusinessConnection.Query<Form_Child_TableInfo_OutPut>("SELECT autoid,tablecode,primarytableid,foreignkey FROM form_childtableinfo WHERE primarytableid=@PrimaryTableId", new Entity.Model.Form_Child_TableInfo { PrimaryTableId = ParentId }).AsList();

        /// <summary>
        /// 获取互斥表
        /// </summary>
        /// <param name="ParentId"></param>
        /// <returns></returns>
        public List<Form_SametableInfo_OutPut> GetForm_SametableInfos(long ParentId) => BusinessConnection.Query<Form_SametableInfo_OutPut>("SELECT autoid,tablecode,primarytableid FROM form_sametableinfo WHERE primarytableid=@PrimaryTableId", new Entity.Model.Form_SametableInfo { PrimaryTableId = ParentId }).AsList();


        /// <summary>
        /// 判断该表是否已经设置过
        /// </summary>
        /// <param name="autoId"></param>
        /// <param name="tableCode"></param>
        /// <returns></returns>
        public bool IstTableodeRepeat(int autoId, string tableCode, string ForeignKey)
        {
            string sql = $"SELECT * FROM form_tableinfo WHERE tablecode='{tableCode}' and foreignkey='{ForeignKey}'";
            if (autoId != 0)
            {
                sql = $"{sql} AND autoid != {autoId}";
            }
            return BusinessConnection.Query<Entity.Model.Form_TableInfo>(sql).Any();
        }

        public double GetSeq()
        {
            Form_TableInfo TableInfo = this.BusinessConnection.QueryFirstOrDefault<Form_TableInfo>("SELECT * FROM form_tableinfo WHERE havedelete=0  ORDER BY autoid DESC LIMIT 0,1");
            if (TableInfo is null) return 1;
            else return (TableInfo.Sequence <= 0 ? 1 : TableInfo.Sequence + 1);
        }

        /// <summary>
        /// 分页获取列表数据
        /// </summary>
        /// <param name="queryModel"></param>
        /// <param name="Total"></param>
        /// <returns></returns>
        public List<PageForm_TableInfoTable> Pages(QueryModel queryModel, out long Total)
        {
            Total = 0;
            string where = "";
            if (!string.IsNullOrEmpty(queryModel.KeyWord))
            {
                where += $"   AND r.tablecode LIKE '%{queryModel.KeyWord}%'";
            }
            if (string.IsNullOrEmpty(queryModel.SortField))
            {
                queryModel.SortField = "sequence";
                queryModel.IsAsc = false;
            }
            else
            {
                queryModel.SortField = queryModel.SortField.ToLower().Replace("dic_", "").Replace("dic", "").Trim();
            }
            if (queryModel.ExceptDataField.Any())
            {
                queryModel.ExceptDataField.ForEach(o =>
                {
                    if (!string.IsNullOrEmpty(o.Value + ""))
                        where += $" AND r.{o.Label}!='{o.Value}'";
                });
            }
            if (queryModel.paramters.Any())
            {
                queryModel.paramters.ForEach(p =>
                {
                    where += $"  and {p.Label} like '%{p.Value}%'  ";
                });
            }
            if (!queryModel.UseDisabledData)
            {
                where += $" AND r.enable=1  ";
            }
            string asc = queryModel.IsAsc ? "ASC" : "DESC";
            queryModel.Page = queryModel.Page == 0 ? 1 : queryModel.Page;
            StringBuilder sql = new StringBuilder();
            sql.Append(@$"SELECT count(r.autoid) as cnt FROM form_tableinfo r WHERE r.havedelete !=1
                        {where}  ;");
            sql.Append(@$"SELECT autoid, foreignkey,flowtablecode, tablecode, enable, havedelete, `sequence`, creator, modifier, creatdate, modifdate FROM form_tableinfo r 
                          WHERE r.havedelete !=1
                        {where}  ORDER BY {queryModel.SortField} {asc} LIMIT {(queryModel.Page - 1) * queryModel.PageSize},{ queryModel.PageSize} ;");
            using (var reader = BusinessConnection.QueryMultiple(sql.ToString()))
            {
                Total = reader.ReadFirstOrDefault<int>();
                List<PageForm_TableInfoTable> pageModules = reader.Read<PageForm_TableInfoTable>().ToList();
                long RowNo = ((queryModel.Page == 0 ? 1 : queryModel.Page) - 1) * queryModel.PageSize + 1;
                pageModules.ForEach(o =>
                {
                    o.RowNo = RowNo;
                    RowNo++;
                });
                return pageModules;
            }
        }

        public bool Add(List<Form_Child_TableInfo> Childs, long id)
        {
            bool b = true;
            BusinessConnection.Execute("DELETE FROM form_childtableinfo WHERE primarytableid=@PrimaryTableId", new Form_Child_TableInfo() { PrimaryTableId = id });
            if (Childs != null && Childs.Any())
            {
                b = BusinessConnection.Insert(Childs) >= 0;
            }
            return b;
        }

        public bool Add(List<Form_SametableInfo> SametableInfos, long id)
        {

            bool b = true;
            BusinessConnection.Execute("DELETE FROM form_sametableinfo WHERE primarytableid=@PrimaryTableId", new Form_SametableInfo() { PrimaryTableId = id });
            if (SametableInfos != null && SametableInfos.Any())
            {
                b = BusinessConnection.Insert(SametableInfos) >= 0;
            }
            return b;
        }

        public List<PageForm_TableInfoTable> Childs(QueryModel queryModel, out long total)
        {
            total = 0;
            string where = "";
            if (!string.IsNullOrEmpty(queryModel.KeyWord))
            {
                where += $"   AND r.tablecode LIKE '%{queryModel.KeyWord}%'";
            }
            if (string.IsNullOrEmpty(queryModel.SortField))
            {
                queryModel.SortField = "sequence";
                queryModel.IsAsc = false;
            }
            else
            {
                queryModel.SortField = queryModel.SortField.ToLower().Replace("dic_", "").Replace("dic", "").Trim();
            }
            if (queryModel.ExceptDataField.Any())
            {
                queryModel.ExceptDataField.ForEach(o =>
                {
                    if (!string.IsNullOrEmpty(o.Value + ""))
                        where += $" AND r.{o.Label}!='{o.Value}'";
                });
            }
            if (queryModel.paramters.Any())
            {
                queryModel.paramters.ForEach(o =>
                {
                    if (!string.IsNullOrEmpty(o.Value + ""))
                        where += $" AND r.{o.Label}='{o.Value}'";
                });
            }
            if (!queryModel.UseDisabledData)
            {
                where += $" AND r.enable=1  ";
            }
            string asc = queryModel.IsAsc ? "ASC" : "DESC";
            queryModel.Page = queryModel.Page == 0 ? 1 : queryModel.Page;
            StringBuilder sql = new StringBuilder();
            sql.Append(@$"SELECT count(r.autoid) as cnt FROM form_childtableinfo r WHERE r.havedelete !=1
                        {where}  ;");
            sql.Append(@$"SELECT autoid, foreignkey,flowtablecode, tablecode, enable, havedelete, `sequence`, creator, modifier, creatdate, modifdate FROM form_childtableinfo r 
                          WHERE r.havedelete !=1
                        {where}  ORDER BY {queryModel.SortField} {asc} LIMIT {(queryModel.Page - 1) * queryModel.PageSize},{ queryModel.PageSize} ;");
            using (var reader = BusinessConnection.QueryMultiple(sql.ToString()))
            {
                total = reader.ReadFirstOrDefault<int>();
                List<PageForm_TableInfoTable> pageModules = reader.Read<PageForm_TableInfoTable>().ToList();
                long RowNo = ((queryModel.Page == 0 ? 1 : queryModel.Page) - 1) * queryModel.PageSize + 1;
                pageModules.ForEach(o =>
                {
                    o.RowNo = RowNo;
                    RowNo++;
                });
                return pageModules;
            }
        }

        /// <summary>
        /// 防止数据库添加重复数据
        /// </summary>
        /// <param name="Model">表数据</param>
        /// <returns></returns>
        public long UnRepeatAdd(Form_TableInfo Model)
        {
            StringBuilder sb = new StringBuilder(@"INSERT INTO form_tableinfo (foreignkey, flowtablecode, tablecode, enable, havedelete
	                                                    , `sequence`, creator, modifier, creatdate, modifdate)
                                                    SELECT @ForeignKey, @FlowTableCode, @TableCode, @Enable, @HaveDelete
	                                                    ,  (select ifnull(max(`sequence`),0)+1 from form_tableinfo), @Creator, @Modifier, now(), now()
                                                    FROM DUAL
                                                    WHERE NOT EXISTS (
	                                                    SELECT *
	                                                    FROM form_tableinfo
	                                                    WHERE tablecode =@TableCode
		                                                    AND foreignkey =@ForeignKey
                                                    );");
            int count = this.BusinessConnection.Execute(sb.ToString(), Model);
            if (count > 0)
            {
                return this.BusinessConnection.ExecuteScalar<long>("select @@identity");
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 根据编码获取表
        /// </summary>
        /// <param name="TableCode"></param>
        /// <returns></returns>
        public List<Form_TableInfoExt> GetTableByCode(string TableCode) => BusinessConnection.Query<Form_TableInfoExt>($@"select autoid,enable ,tablecode ,havedelete from form_tableinfo ft where ft.tablecode in({TableCode});").AsList();
    }
}
